# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：     query
   Description :
   Author :       Flyoung
   date：          2023/9/7
-------------------------------------------------
   Change Activity:
                   2023/9/7:
-------------------------------------------------
"""
from dao.models import *
from sqlalchemy.sql.expression import func


def package_job(job):
    # 岗位所在城市名
    city_name = City.query.filter(City.city_id == job.city_id).one().city_name
    # 岗位所需学历
    education_list = db.session.query(Education).filter(
        JobEducation.job_id == job.job_id,
        JobEducation.education_id == Education.education_id
    ).all()
    # 岗位所拥有的福利
    welfare_list = db.session.query(Welfare).filter(
        JobWelfare.job_id == job.job_id,
        JobWelfare.welfare_id == Welfare.welfare_id
    ).all()

    job.city_name = city_name
    job.welfare_name_list = [welfare.welfare_name for welfare in welfare_list]
    job.education_name_list = [education.education_name for education in education_list]
    return model_dict(job)


def job_query_by_name(job_name, page, page_size):
    """
    查询工作名为 job_name 的工作
    :param page_size:
    :param page:
    :param job_name:
    :return:
    """
    job_list = Job.query.filter(Job.job_name.like(f'%{job_name}%')).paginate(page=page, per_page=page_size)
    result = job_list.__dict__
    result['items'] = [model_dict(job) for job in job_list.items]
    return model_dict(result)


def job_query(job_id):
    """
    查询某个工作
    :param job_id:
    :return:
    """
    job = Job.query.get(job_id)
    return package_job(job)


def job_page_query(page, page_size):
    """
    job 分页查询
    :return:
    """
    job_list = Job.query.paginate(page=page, per_page=page_size)
    result = job_list.__dict__
    result['items'] = [model_dict(job) for job in job_list.items]
    return model_dict(result)


def city_job_proportion_query():
    """
    查询每个城市所拥有的岗位数量
    :return:
    """
    city_list = City.query.all()
    city_job_proportion = dict()
    for city in city_list:
        city_job_proportion[city.city_name] = Job.query.filter(Job.city_id == city.city_id).count()
    return city_job_proportion


def welfare_job_proportion_query():
    """
    查询每种福利的出现频次
    :return:
    """
    welfare_list = Welfare.query.all()
    welfare_job_proportion = dict()
    for welfare in welfare_list:
        welfare_job_proportion[welfare.welfare_name] = JobWelfare.query.filter(
            JobWelfare.welfare_id == welfare.welfare_id
        ).count()
    return welfare_job_proportion


def tech_job_proportion_query():
    """
    查询每种技术出现的频次
    :return:
    """
    tech_list = Tech.query.all()
    tech_job_proportion = dict()
    for tech in tech_list:
        tech_job_proportion[tech.tech_name] = JobTech.query.filter(
            JobTech.tech_id == tech.tech_id
        ).count()
    return tech_job_proportion


def salary_compare(job_count=10, job_limit=5):
    """
    薪资对比
    :return:
    """
    educations = Education.query.all()
    education_names = [e.education_name for e in educations]
    jobs = Job.query.with_entities(Job.job_name, func.count(Job.job_id).label('count')) \
        .group_by(Job.job_name) \
        .order_by(func.count(Job.job_id).desc()) \
        .limit(job_count) \
        .all()
    job_names = [j.job_name for j in jobs]
    data = dict()
    data['x'] = education_names
    data['y'] = job_names
    data['z'] = list()
    unique = set()
    for job_name in job_names:
        some_job = Job.query.filter(Job.job_name == job_name).limit(job_limit)
        for job in some_job:
            je = JobEducation.query.filter(JobEducation.job_id == job.job_id).one()
            e = Education.query.filter(Education.education_id == je.education_id).one()
            e_idx = education_names.index(e.education_name)
            j_idx = job_names.index(job_name)
            if (e_idx, j_idx) not in unique:
                data['z'].append(
                    [
                        e_idx,
                        j_idx,
                        round((job.salary_lower_limit + job.salary_upper_limit) / 2, 2)
                    ]
                )
                unique.add((e_idx, j_idx))
    return data
